package com.is.control;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.is.dbconn.DBConnect;
import com.is.model.CourseClass;

public class CourseClassControl {
	
	/**
	 * 
	 * @param c
	 * @return
	 */
	public static int insertCourseClass(CourseClass c){
		DBConnect dbc  = new DBConnect();
		String sql = "insert into course_class(courseId, classId,prevTotalClass, realTotalClass, stuyear,uid,state) values (?,?,?,?,?,?,?)";
		dbc.prepareStatement(sql);
		ResultSet rs= null;
		int maxId = 0;
		try{
				dbc.setInt(1, c.getCourseId());
				dbc.setInt(2, c.getClassId());
				dbc.setString(3, c.getPrevTotalClass());
				dbc.setString(4,c.getRealTotalClass());
				dbc.setInt(5,c.getStuyear());
				dbc.setInt(6, c.getUid());
				dbc.setInt(7, c.getState());
				dbc.executeUpdate();
				rs = dbc.getPreparedStatement().getGeneratedKeys();
				if(rs.next()){
					maxId = rs.getInt(1);
				}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("Insert Error");
		} finally{
			dbc.close();
		}
		return maxId;
	}
	
	/**
	 * 更改课程状态
	 * @param id
	 * @return
	 */
	public static boolean updateStateById(int id,int state) {
		DBConnect dbConnect = new DBConnect();
		String sql = "update course_class set state = '"+state+"' where id = '"+id+"'";
		//System.out.println(sql);
		dbConnect.prepareStatement(sql);
		boolean flag= false;
		try {
			flag  = dbConnect.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("updateStateById ERROR");
		}finally{
			dbConnect.close();
		}
		return flag;
	}
	/**
	 * 
	 * @param id
	 * @param state
	 * @return
	 */
	public static boolean updatePrevTotalClassById(int id,String state) {
		DBConnect dbConnect = new DBConnect();
		String sql = "update course_class set prevTotalClass = '"+state+"' where id = '"+id+"'";
		dbConnect.prepareStatement(sql);
		boolean flag= false;
		try {
			flag  = dbConnect.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("updatePrevTotalClassById ERROR");
		}finally{
			dbConnect.close();
		}
		return flag;
	}
	public static boolean deleteCourseClassById(int id) {
		DBConnect dbConnect = new DBConnect();
		String sql = "delete from course_class where id =" + id;
		dbConnect.prepareStatement(sql);
		try{
			dbConnect.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			System.out.println("deleteCourseClassById ERROR");
			dbConnect.close();
		}
		return false;
	}
	/**
	 * 
	 * @param id
	 * @return
	 */
	public static CourseClass getCourseClassById(int id){
		DBConnect dbc = new DBConnect();
		String sql = "select * from course_class where id=" + id;
		dbc.prepareStatement(sql);
		ResultSet rs = null;
		CourseClass c = new CourseClass();
		try{
			rs = dbc.executeQuery();
			while(rs.next()){
				c.setCourseId(rs.getInt("courseId"));
				c.setClassId(rs.getInt("classId"));
				c.setPrevTotalClass(rs.getString("prevTotalClass"));
				c.setRealTotalClass(rs.getString("realTotalClass"));
				c.setStuyear(rs.getInt("stuyear"));
				c.setUid(rs.getInt("uid"));
				c.setId(rs.getInt("id"));
				c.setState(rs.getInt("state"));
			}
			return c;
		} catch(Exception e){
			e.printStackTrace();
			System.out.println("调用getCourseClassById函数失败");
			return null;
		} finally{
			dbc.close();
		}
		
	}
	
	/**
	 * 判断该条数据是否已经存在，存在返回true
	 * @param uid
	 * @param stuyear
	 * @param courseId
	 * @param classId
	 * @return
	 */
	public static boolean isInserted(int stuyear,int courseId, int classId){
		DBConnect dbConnect=  new DBConnect();
		String sql = "select id from course_class where  classId = '"+classId+"' and courseId = '"+courseId+"' and stuyear='"+stuyear+"'";
		ResultSet rs = null;
		boolean flag=false;
		dbConnect.prepareStatement(sql);
		try {
			rs = dbConnect.executeQuery();
			if(rs.next()){
				flag = true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			dbConnect.close();
		}
		return flag;
	}
     
	/**
      * 获取某个老师所有教课的情况（当uid为0时获取某个学期的任课情况）
      * @param uid
      * @param stuyear
      * @return
      */
	 public static List<CourseClass> getAllCourseClassIdByUidAndStuyear(int uid,int stuyear){
		 DBConnect dbConnect  =new DBConnect();
		 String sql = "";
		 if(uid==0){
			  sql = "select * from course_class where stuyear= '"+stuyear+"' order by uid";
		 }else{
		      sql = "select * from course_class where uid ='"+uid+"' and stuyear= '"+stuyear+"' ";
		 }
		 ResultSet rSet = null;
		 List<CourseClass> classes  =new ArrayList<CourseClass>();
		 dbConnect.prepareStatement(sql);
		 try {
			rSet = dbConnect.executeQuery();
			while(rSet.next()){
				CourseClass cc =new CourseClass();
				cc.setClassId(rSet.getInt("classId"));
				cc.setCourseId(rSet.getInt("courseId"));
				cc.setId(rSet.getInt("id"));
				cc.setPrevTotalClass(rSet.getString("prevTotalClass"));
				cc.setRealTotalClass(rSet.getString("realTotalClass"));
				cc.setUid(rSet.getInt("uid"));
				cc.setStuyear(rSet.getInt("stuyear"));
				cc.setState(rSet.getInt("state"));
				classes.add(cc);
			}
			return classes;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getAllClassIdByUid error");
		}finally{
			dbConnect.close();
		}
		return null;
	 }
	 /**
      * 获取某个班级所有课程的情况（当stuyear为0时获取某个班级所有课程）
      * @param uid
      * @param stuyear
      * @return
      */
	 public static List<CourseClass> getAllCourseClassIdByClassIdAndStuyear(int classId,int stuyear){
		 DBConnect dbConnect  =new DBConnect();
		 String sql = "";
		 if(stuyear==0){
			  sql = "select * from course_class where classId ='"+classId+"'";
		 }else{
		      sql = "select * from course_class where classId ='"+classId+"' and stuyear= '"+stuyear+"'";
		 }
		 ResultSet rSet = null;
		 List<CourseClass> classes  =new ArrayList<CourseClass>();
		 dbConnect.prepareStatement(sql);
		 try {
			rSet = dbConnect.executeQuery();
			while(rSet.next()){
				CourseClass cc =new CourseClass();
				cc.setClassId(rSet.getInt("classId"));
				cc.setCourseId(rSet.getInt("courseId"));
				cc.setId(rSet.getInt("id"));
				cc.setPrevTotalClass(rSet.getString("prevTotalClass"));
				cc.setRealTotalClass(rSet.getString("realTotalClass"));
				cc.setUid(rSet.getInt("uid"));
				cc.setStuyear(rSet.getInt("stuyear"));
				cc.setState(rSet.getInt("state"));
				classes.add(cc);
			}
			return classes;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getAllClassIdByClassIdAndStuyear error");
		}finally{
			dbConnect.close();
		}
		return null;
	 }
/**
 * 
 * @param ccid
 * @param classAmout
 * @return
 */
	 public static boolean  updatetRealToalClassById(int ccid,String classAmout) {
		 double c = Double.parseDouble(classAmout);
		 DBConnect dbc = new DBConnect();
		 String sql = "update course_class set realTotalClass =realTotalClass+ "+c+" where id='"+ccid+"' ";
		 dbc.prepareStatement(sql);
		 boolean flag=false;
		 try {
			flag = dbc.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("updatetRealToalClassById ERROR");
		}finally{
			dbc.close();
		}
		return flag;
	}
	 
	 
	 public  static void main(String args[])  {
		CourseClass cc = new CourseClass();
		cc.setClassId(1);
		cc.setCourseId(2);
		cc.setPrevTotalClass("");
		System.out.println(insertCourseClass(cc));
	}
}
